const express = require('express');
const mysql = require('mysql2');
const cors = require('cors');
const bcrypt = require('bcrypt');
const jwt = require('jsonwebtoken');
const app = express();
const port = 5001;

const saltRounds = 10;

// 使用 CORS 中间件
app.use(cors());
app.use(express.json());

// 创建 MySQL 连接池
const db = mysql.createPool({
  host: '127.0.0.1',
  user: 'root', // 你的 MySQL 用户名
  password: '200408260730p', // 你的 MySQL 密码
  database: 'my_reactdb', // 你的数据库名称
  waitForConnections: true,
  connectionLimit: 10
});

// 连接到 MySQL
db.getConnection((err, connection) => {
  if (err) {
    console.error('Error connecting to MySQL:', err);
    return;
  }
  console.log('Connected to MySQL database');
  connection.release();
});

// 查询所有用户
db.query('SELECT * FROM login', async (err, results) => {
  if (err) {
    console.error('Error fetching users:', err);
    return;
  }

  for (const login of results) {
    if (!login.password.startsWith('$2b$')) { // 检查是否是明文密码
      try {
        const hashedPassword = await bcrypt.hash(login.password, saltRounds);

        // 更新用户密码
        db.query('UPDATE login SET password = ? WHERE id = ?', [hashedPassword, login.id], (err) => {
          if (err) {
            console.error('Error updating user:', err);
          } else {
            console.log(`Updated password for user ${login.username}`);
          }
        });
      } catch (error) {
        console.error('Error hashing password:', error);
      }
    }
  }
});

// 登录接口
app.post('/user/login', async (req, res) => {
  const { username, password } = req.body;

  if (!username || !password) {
    return res.status(400).json({ code: 400, message: 'Username and password are required' });
  }

  const query = 'SELECT * FROM login WHERE username = ?';
  db.query(query, [username], async (err, results) => {
    if (err) {
      return res.status(500).json({ code: 500, message: 'Database error' });
    }

    if (results.length === 0) {
      return res.status(401).json({ code: 401, message: 'Invalid username or password' });
    }

    const user = results[0];
    const match = await bcrypt.compare(password, user.password);

    if (!match) {
      return res.status(401).json({ code: 401, message: 'Invalid username or password' });
    }

    // 生成 JWT
    const token = jwt.sign({ userId: user.id }, 'your-secret-key', { expiresIn: '1h' });

    res.json({ code: 200, message: 'Login successful', token });
  });
});

// 获取新生列表（分页）
app.post('/freshman/list', async (req, res) => {
  try {
    const { pageSize, page, name, sex, emailId, phone, departmentId, major, studentId } = req.body;

    // 参数校验（可选）
    if (!pageSize || !page) {
      return res.status(400).json({ error: 'Invalid parameters' });
    }

    // 构建查询语句
    let query = 'SELECT * FROM Freshman WHERE 1=1';
    const values = [];

    if (name) {
      query += ' AND name = ?';
      values.push(name);
    }
    if (sex) {
      query += ' AND sex = ?';
      values.push(sex);
    }
    if (emailId) {
      query += ' AND email_id = ?';
      values.push(emailId);
    }
    if (phone) {
      query += ' AND phone = ?';
      values.push(phone);
    }
    if (departmentId) {
      query += ' AND department_id = ?';
      values.push(departmentId);
    }
    if (major) {
      query += ' AND major = ?';
      values.push(major);
    }
    if (studentId) {
      query += ' AND student_id = ?';
      values.push(studentId);
    }

    // 分页
    query += ' LIMIT ? OFFSET ?';
    values.push(pageSize);
    values.push((page - 1) * pageSize);

    db.query(query, values, (err, results) => {
      if (err) {
        console.error('数据库查询错误:', err);
        return res.status(500).json({ error: err.message });
      }
      res.json(results);
    });
  } catch (error) {
    console.error('Database Error:', error);
    res.status(500).json({ error: 'Internal Server Error' });
  }
});
// 添加新生
app.post('/freshman/add', async (req, res) => {
  try {
      const { name, sex, emailId, phone, departmentId, major, studentId } = req.body;

      // 参数校验
      if (!name || !sex || !emailId || !phone || !departmentId || !major || !studentId) {
          return res.status(400).json({ code: 400, message: 'All fields are required' });
      }

      // 插入数据到数据库
      const query = `
          INSERT INTO Freshman (name, sex, emailId, phone, departmentId, major, studentId)
          VALUES (?, ?, ?, ?, ?, ?, ?)
      `;
      const values = [name, sex, emailId, phone, departmentId, major, studentId];

      db.query(query, values, (err, results) => {
          if (err) {
              console.error('数据库插入错误:', err);
              return res.status(500).json({ code: 500, message: 'Database error' });
          }
          res.json({ code: 200, message: 'Freshman added successfully', id: results.insertId });
      });
  } catch (error) {
      console.error('Error adding freshman:', error);
      res.status(500).json({ code: 500, message: 'Internal Server Error' });
  }
});

// 编辑新生接口
app.put('/freshman/:id', async (req, res) => {
  try {
      const { id } = req.params;
      const { name, sex, emailId, phone, departmentId, major, studentId } = req.body;

      // 参数校验
      if (!id) {
          return res.status(400).json({ code: 400, message: 'ID is required' });
      }

      if (!name || !sex || !emailId || !phone || !departmentId || !major || !studentId) {
          return res.status(400).json({ code: 400, message: 'All fields are required' });
      }

      // 更新数据到数据库
      const query = `
          UPDATE Freshman 
          SET name = ?, sex = ?, emailId = ?, phone = ?, departmentId = ?, major = ?, studentId = ?
          WHERE id = ?
      `;
      const values = [name, sex, emailId, phone, departmentId, major, studentId, id];

      db.query(query, values, (err, results) => {
          if (err) {
              console.error('数据库更新错误:', err);
              return res.status(500).json({ code: 500, message: 'Database error' });
          }

          if (results.affectedRows === 0) {
              return res.status(404).json({ code: 404, message: 'Freshman not found' });
          }

          res.json({ code: 200, message: 'Freshman updated successfully' });
      });
  } catch (error) {
      console.error('Error updating freshman:', error);
      res.status(500).json({ code: 500, message: 'Internal Server Error' });
  }
});
// 删除新生接口
app.delete('/freshman/:id', async (req, res) => {
  try {
      const { id } = req.params;

      // 参数校验
      if (!id) {
          return res.status(400).json({ code: 400, message: 'ID is required' });
      }

      // 删除数据从数据库
      const query = 'DELETE FROM Freshman WHERE id = ?';
      const values = [id];

      db.query(query, values, (err, results) => {
          if (err) {
              console.error('数据库删除错误:', err);
              return res.status(500).json({ code: 500, message: 'Database error' });
          }

          if (results.affectedRows === 0) {
              return res.status(404).json({ code: 404, message: 'Freshman not found' });
          }

          res.json({ code: 200, message: 'Freshman deleted successfully' });
      });
  } catch (error) {
      console.error('Error deleting freshman:', error);
      res.status(500).json({ code: 500, message: 'Internal Server Error' });
  }
});
// 启动服务器
app.listen(port, () => {
  console.log(`Server is running on port ${port}`);
});